package cn.starremote.component.utils;

import cn.starremote.component.annotation.ExcelColumn;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.BooleanUtils;
import org.apache.commons.lang3.CharUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.math.NumberUtils;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.lang.reflect.Constructor;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.concurrent.atomic.AtomicInteger;
import java.util.stream.Collectors;
import java.util.stream.Stream;

/**
 * ExcelUtil 工具类
 * @author wangjw
 */
@Slf4j
public class ExcelUtil {

    public final static String XLS = "xls";
    public final static String XLSX = "xlsx";

    private static final SimpleDateFormat SIMPLE_DATE_FORMAT = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

    private static void checkExcel(File file) {
        check(file);
        String fileName = file.getName();
        if (!fileName.endsWith(XLS) && !fileName.endsWith(XLSX)) {
            try {
                throw new IOException(fileName + " is not excel file!");
            } catch (IOException e) {
                throw new RuntimeException(e);
            }
        }
    }

    private static void check(File file) {
        if (null == file) {
            log.error("file not found!");
            try {
                throw new FileNotFoundException("file not found!");
            } catch (FileNotFoundException e) {
                e.printStackTrace();
                log.error(e.getMessage());
            }
        }
    }

    private static Workbook readFile(File file) {
        checkExcel(file);
        String fileName = file.getName();
        Workbook workbook = null;
        try {
            InputStream inputStream = new FileInputStream(file);
            if (fileName.endsWith(XLSX)) {
                workbook = new XSSFWorkbook(inputStream);
            } else if (fileName.endsWith(XLS)) {
                workbook = new HSSFWorkbook(inputStream);
            } else {
                throw new Exception(fileName + " is not excel file!");
            }
            inputStream.close();
            workbook.close();
        } catch (IOException e) {
            e.printStackTrace();
            log.error(e.getMessage());
        } catch (Exception e) {
            e.printStackTrace();
        }
        return workbook;
    }

    private static void writeFile(Workbook workbook, File file) {
        try {
            OutputStream fileOut = new FileOutputStream(file.getPath());
            workbook.write(fileOut);
            fileOut.close();
            workbook.close();
        } catch (IOException e) {
            e.printStackTrace();
            log.error(e.getMessage());
        }
    }

    private static List<Object[]> readSheet(Sheet sheet) {
        List<Object[]> rowList = new ArrayList<>();
        int lastRowNum = sheet.getLastRowNum();
        for (int rowIndex = 0; rowIndex <= lastRowNum; rowIndex++) {
            Row row = sheet.getRow(rowIndex);
            if (null == row) {
                rowList.add(new String[0]);
                continue;
            }
            int lastCellNum = row.getLastCellNum();
            Object[] valueArray = new Object[lastCellNum];
            for (int cellIndex = 0; cellIndex < lastCellNum; cellIndex++) {
                Cell cell = row.getCell(cellIndex);
                if (null == cell) {
                    valueArray[cellIndex] = null;
                    continue;
                }
                Object value = readCell(cell);
                valueArray[cellIndex] = value;
            }
            rowList.add(valueArray);
        }
        return rowList;
    }

    /**
     * 读 excel
     * 单个 sheet
     *
     * @param file excel
     * @return Map
     */
    public static List<Object[]> readSingle(File file) {
        Workbook workbook = readFile(file);
        if (null == workbook) {
            return null;
        }
        Sheet sheet = workbook.getSheetAt(0);
        if (null == sheet) {
            return null;
        }
        List<Object[]> rowList = readSheet(sheet);
        try {
            workbook.close();
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
        return rowList;
    }

    /**
     * 读 excel
     * 多个 sheet
     *
     * @param file excel
     * @return Map
     */
    public static Map<String, List<Object[]>> read(File file) {
        Workbook workbook = readFile(file);
        if (null == workbook) {
            return null;
        }
        LinkedHashMap<String, List<Object[]>> data = new LinkedHashMap<>(1 << 4);
        int numberOfSheets = workbook.getNumberOfSheets();
        for (int sheetIndex = 0; sheetIndex < numberOfSheets; sheetIndex++) {
            Sheet sheet = workbook.getSheetAt(sheetIndex);
            if (null == sheet) {
                data.put(null, null);
                continue;
            }
            String sheetName = sheet.getSheetName();
            List<Object[]> rowList = readSheet(sheet);
            data.put(sheetName, rowList);
        }
        try {
            workbook.close();
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
        return data;
    }

    private static void writeSheet(Sheet sheet, List<Object[]> rowList) {
        int size = rowList.size();
        for (int rowIndex = 0; rowIndex < size; rowIndex++) {
            Object[] valueArray = rowList.get(rowIndex);
            Row row = sheet.createRow(rowIndex);
            int length = valueArray.length;
            for (int cellIndex = 0; cellIndex < length; cellIndex++) {
                Cell cell = row.createCell(cellIndex);
                Object value = valueArray[cellIndex];
                writeCell(cell, value);
            }
        }
    }

    /**
     * 写 excel
     * 单个 sheet
     *
     * @param path    String
     * @param rowList List<Object[]>
     * @return File
     */
    public static File write(String path, List<Object[]> rowList) {
        File file = new File(path);
        checkExcel(file);
        if (null == rowList) {
            return file;
        }
        // Workbook workbook = new HSSFWorkbook();
        Workbook workbook = new XSSFWorkbook();
        /* CreationHelper createHelper = workbook.getCreationHelper(); */
        String sheetName = "Sheet1";
        Sheet sheet = workbook.createSheet(sheetName);
        writeSheet(sheet, rowList);
        writeFile(workbook, file);
        return file;
    }

    /**
     * 写 excel
     * 多个 sheet
     *
     * @param path         String
     * @param workbookData LinkedHashMap
     * @return File
     */
    public static File write(String path, Map<String, List<Object[]>> workbookData) {
        File file = new File(path);
        checkExcel(file);
        if (null == workbookData) {
            return file;
        }
        // Workbook workbook = new HSSFWorkbook();
        Workbook workbook = new XSSFWorkbook();
        /* CreationHelper createHelper = workbook.getCreationHelper(); */
        for (Map.Entry<String, List<Object[]>> entry : workbookData.entrySet()) {
            String sheetName = entry.getKey();
            List<Object[]> rowList = entry.getValue();
            Sheet sheet = workbook.createSheet(sheetName);
            writeSheet(sheet, rowList);
        }
        writeFile(workbook, file);
        return file;
    }

    private static Object readCell(Cell cell) {
        if (null == cell) {
            return null;
        }
        Object value = null;
        switch (cell.getCellType()) {
            case _NONE:
                break;
            case NUMERIC:
                if (org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(cell)) {
                    value = cell.getDateCellValue();
                } else {
                    value = cell.getNumericCellValue();
                }
                break;
            case STRING:
                value = cell.getStringCellValue();
                break;
            case FORMULA:
                value = cell.getCellFormula();
                break;
            case BLANK:
                value = "";
                break;
            case BOOLEAN:
                value = cell.getBooleanCellValue();
                break;
            case ERROR:
                value = cell.getErrorCellValue();
                break;
            default:
                value = null;
                break;
        }
        return value;
    }

    private static void writeCell(Cell cell, Object value) {
        if (null == value || "".equals(value)) {
            /// cell.setCellType(CellType._NONE);
            cell.setCellType(CellType.BLANK);
        } else if (value instanceof Number) {
            cell.setCellType(CellType.NUMERIC);
            cell.setCellValue(Double.parseDouble(String.valueOf(value)));
        } else if (value instanceof String) {
            cell.setCellType(CellType.STRING);
            cell.setCellValue(String.valueOf(value));
///        } else if (value instanceof String) {
///            cell.setCellType(CellType.FORMULA);
        } else if (value instanceof Boolean) {
            cell.setCellType(CellType.BOOLEAN);
            cell.setCellValue((Boolean) value);
        } else if (value instanceof Error || value instanceof Exception) {
            cell.setCellType(CellType.ERROR);
        } else if (value instanceof Date) {
            cell.setCellType(CellType.STRING);
            Date date = (Date) value;
            String dataTimeString;
            dataTimeString = SIMPLE_DATE_FORMAT.format(date);
            cell.setCellValue(dataTimeString);
        } else if (value instanceof Calendar) {
            cell.setCellType(CellType.NUMERIC);
            cell.setCellValue((Calendar) value);
        } else {
            cell.setCellType(CellType.STRING);
            cell.setCellValue(String.valueOf(value));
        }
    }

    /**
     * @param file  File
     * @param clazz Class<T>
     * @param <T>   <T>
     * @return <T> List<T>
     */
    public static <T> List<T> read(File file, Class<T> clazz) {

        List<T> list = new ArrayList<>();
        Workbook workbook = readFile(file);
        Map<String, List<Field>> classMap = new HashMap<>(1 << 4);
        List<Field> fields = Stream.of(clazz.getDeclaredFields()).collect(Collectors.toList());
        fields.forEach(
                field -> {
                    ExcelColumn annotation = field.getAnnotation(ExcelColumn.class);
                    if (annotation != null) {
                        String value = annotation.value();
                        if (StringUtils.isBlank(value)) {
                            return;
                        }
                        if (!classMap.containsKey(value)) {
                            classMap.put(value, new ArrayList<>());
                        }
                        field.setAccessible(true);
                        classMap.get(value).add(field);
                    }
                }
        );
        Map<Integer, List<Field>> reflectionMap = new HashMap<>(1 << 4);
        Sheet sheet = workbook.getSheetAt(0);

        boolean firstRow = true;
        for (int i = sheet.getFirstRowNum(); i <= sheet.getLastRowNum(); i++) {
            Row row = sheet.getRow(i);
            if (firstRow) {
                for (int j = row.getFirstCellNum(); j <= row.getLastCellNum(); j++) {
                    Cell cell = row.getCell(j);
                    String cellValue = getCellValue(cell);
                    if (classMap.containsKey(cellValue)) {
                        reflectionMap.put(j, classMap.get(cellValue));
                    }
                }
                firstRow = false;
            } else {
                if (row == null) {
                    continue;
                }
                try {
                    T t = clazz.newInstance();
                    boolean allBlank = true;
                    for (int j = row.getFirstCellNum(); j <= row.getLastCellNum(); j++) {
                        if (reflectionMap.containsKey(j)) {
                            Cell cell = row.getCell(j);
                            String cellValue = getCellValue(cell);
                            if (StringUtils.isNotBlank(cellValue)) {
                                allBlank = false;
                            }
                            List<Field> fieldList = reflectionMap.get(j);
                            fieldList.forEach(
                                    x -> {
                                        try {
                                            handleField(t, cellValue, x);
                                        } catch (Exception e) {
                                            log.error(String.format("reflect field:%s value:%s exception!", x.getName(), cellValue), e);
                                        }
                                    }
                            );
                        }
                    }
                    if (!allBlank) {
                        list.add(t);
                    } else {
                        log.warn(String.format("row:%s is blank ignore!", i));
                    }
                } catch (Exception e) {
                    throw new RuntimeException(e);
                }
            }
        }

        return list;
    }

    /**
     * @param path  String
     * @param clazz Class<T>
     * @param list  List<T>
     * @return File
     */
    public static <T> File write(String path, Class<T> clazz, List<T> list) {
        String sheetName = "Sheet1";
        Field[] fields = clazz.getDeclaredFields();
        List<Field> fieldList = Arrays.stream(fields)
                .filter(field -> {
                    ExcelColumn annotation = field.getAnnotation(ExcelColumn.class);
                    if (annotation != null && annotation.order() > 0) {
                        field.setAccessible(true);
                        return true;
                    }
                    return false;
                }).sorted(Comparator.comparing(field -> {
                    int order = 0;
                    ExcelColumn annotation = field.getAnnotation(ExcelColumn.class);
                    if (annotation != null) {
                        order = annotation.order();
                    }
                    return order;
                })).collect(Collectors.toList());

        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet(sheetName);
        AtomicInteger ai = new AtomicInteger();
        {
            Row row = sheet.createRow(ai.getAndIncrement());
            AtomicInteger aj = new AtomicInteger();
            fieldList.forEach(field -> {
                ExcelColumn annotation = field.getAnnotation(ExcelColumn.class);
                String columnName = "";
                int columnWidth = 10;
                if (annotation != null) {
                    columnName = annotation.value();
                    columnWidth = annotation.width();
                }

                sheet.setColumnWidth(aj.get(), (int) (columnWidth * 256 * 1.2));

                Cell cell = row.createCell(aj.getAndIncrement());
                CellStyle cellStyle = workbook.createCellStyle();
                cellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
                cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
                cellStyle.setAlignment(HorizontalAlignment.CENTER);
                cellStyle.setFillForegroundColor(IndexedColors.LIME.getIndex());
                cellStyle.setBorderLeft(BorderStyle.THIN);
                cellStyle.setBorderRight(BorderStyle.THIN);

                Font font = workbook.createFont();
                font.setBold(true);
                cellStyle.setFont(font);
                cell.setCellStyle(cellStyle);
                cell.setCellValue(columnName);
            });
        }
        if (null != list && !list.isEmpty()) {
            list.forEach(t -> {
                Row row = sheet.createRow(ai.getAndIncrement());
                AtomicInteger aj = new AtomicInteger();
                fieldList.forEach(field -> {
                    Class<?> type = field.getType();
                    Object value = "";
                    try {
                        value = field.get(t);
                    } catch (Exception e) {
                        e.printStackTrace();
                    }
                    Cell cell = row.createCell(aj.getAndIncrement());
                    if (value != null) {
                        if (type == Date.class) {
                            cell.setCellValue(value.toString());
                        } else {
                            cell.setCellValue(value.toString());
                        }
                        cell.setCellValue(value.toString());
                    }
                });
            });
        }
        workbook.getSheet(sheetName).createFreezePane(0, 1, 0, 1);

        if (StringUtils.isNotBlank(path)) {
            return buildExcelFile(path, workbook);
        }
        return null;
    }

    private static <T> void handleField(T t, String value, Field field) throws Exception {
        Class<?> type = field.getType();
        if (type == null || type == void.class || StringUtils.isBlank(value)) {
            return;
        }
        if (type == Object.class) {
            field.set(t, value);
            //数字类型
        } else if (type.getSuperclass() == null || type.getSuperclass() == Number.class) {
            if (type == int.class || type == Integer.class) {
                field.set(t, NumberUtils.toInt(value));
            } else if (type == long.class || type == Long.class) {
                field.set(t, NumberUtils.toLong(value));
            } else if (type == byte.class || type == Byte.class) {
                field.set(t, NumberUtils.toByte(value));
            } else if (type == short.class || type == Short.class) {
                field.set(t, NumberUtils.toShort(value));
            } else if (type == double.class || type == Double.class) {
                field.set(t, NumberUtils.toDouble(value));
            } else if (type == float.class || type == Float.class) {
                field.set(t, NumberUtils.toFloat(value));
            } else if (type == char.class || type == Character.class) {
                field.set(t, CharUtils.toChar(value));
            } else if (type == boolean.class) {
                field.set(t, BooleanUtils.toBoolean(value));
            } else if (type == BigDecimal.class) {
                field.set(t, new BigDecimal(value));
            }
        } else if (type == Boolean.class) {
            field.set(t, BooleanUtils.toBoolean(value));
        } else if (type == Date.class) {
            //
            field.set(t, value);
        } else if (type == String.class) {
            field.set(t, value);
        } else {
            Constructor<?> constructor = type.getConstructor(String.class);
            field.set(t, constructor.newInstance(value));
        }
    }

    private static String getCellValue(Cell cell) {
        if (cell == null) {
            return "";
        }
        if (cell.getCellType() == CellType.NUMERIC) {
            if (org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(cell)) {
                return HSSFDateUtil.getJavaDate(cell.getNumericCellValue()).toString();
            } else {
                return new BigDecimal(cell.getNumericCellValue()).toString();
            }
        } else if (cell.getCellType() == CellType.STRING) {
            return StringUtils.trimToEmpty(cell.getStringCellValue());
        } else if (cell.getCellType() == CellType.FORMULA) {
            return StringUtils.trimToEmpty(cell.getCellFormula());
        } else if (cell.getCellType() == CellType.BLANK) {
            return "";
        } else if (cell.getCellType() == CellType.BOOLEAN) {
            return String.valueOf(cell.getBooleanCellValue());
        } else if (cell.getCellType() == CellType.ERROR) {
            return "ERROR";
        } else {
            return cell.toString().trim();
        }

    }

    /**
     * @param path     String
     * @param workbook Workbook
     * @return File
     */
    private static File buildExcelFile(String path, Workbook workbook) {
        File file = new File(path);
        if (!file.exists()) {
            boolean b = file.mkdirs();
        }
        if (file.exists()) {
            boolean b = file.delete();
        }
        try {
            workbook.write(new FileOutputStream(file));
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
        return file;
    }

}
